<?php 
    class PublicDataDAL{

        //获取公寓列表
        public function getApartmentList($property=0,$status=1){
            $result=null;
            $query_all="select apartment_id,apartment_name,abbreviation_name,
            floor_number,property,remarks,add_time,status 
            from tb_apartment_info where status=:status";

            $query_property="select apartment_id,apartment_name,abbreviation_name,
            floor_number,property,remarks,add_time,status 
            from tb_apartment_info where property=:property and status=:status";

            $pdo=myPDO::getInstance();
            /*switch($property){
                case 0:break;
                case 1:break;
                case 2:break;
                case 3:break;
                default:break;
            }*/
            if((int)$property===0){
                $statement=$pdo->prepare($query_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":status",$status);
            }else{
                $statement=$pdo->prepare($query_property,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                $statement->bindValue(":property",$property);
                $statement->bindValue(":status",$status);
            }
            if($statement->execute()){
                $result=$statement->fetchAll(PDO::FETCH_ASSOC);
            }

            return $result;
        }

        //获取辅导员部门列表
        public function getInstructorDepartment($instructorId){
            $query="select distinct(tb_department_list.department_id),tb_department_list.department_name from tb_instructor_info,tb_department_list,tb_major_list 
            where tb_instructor_info.status=1 
            and tb_instructor_info.major_id=tb_major_list.major_id 
            and tb_major_list.department_id=tb_department_list.department_id 
            and tb_instructor_info.uid=:uid;";

            $pdo=myPDO::getInstance();
            
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorId);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }

        //获取部门列表
        public function getDepartmentList($isALL=false){
            $result=null;
            $query="select department_id,department_name from tb_department_list 
            where tb_department_list.status=1 ".
            ($isALL?'':"and department_id!=0").";";
            $pdo=$pdo=myPDO::getInstance();

            $row=$pdo->query($query);
            $result=$row->fetchAll(PDO::FETCH_ASSOC);
            $row->closeCursor();
            return $result;
        }
        //获取角色列表
        public function getRoleList(){
            $result=null;
            $query="select name,level as value from tb_level_list;";
            $pdo=$pdo=myPDO::getInstance();

            $row=$pdo->query($query);
            $result=$row->fetchAll(PDO::FETCH_ASSOC);
            $row->closeCursor();
            return $result;
        }

        //获取专业列表
        public function getMajorList($uid='',$grade=0,$major=0){
            $query="select major_id,major_name from tb_major_list;";
            $pdo=myPDO::getInstance();
            
            $row=$pdo->query($query);
            $result=$row->fetchAll(PDO::FETCH_ASSOC);
            $row->closeCursor();

            return $result;
        }

        public function getMajorName($major_id){
            $query="select major_id,major_name from tb_major_list where major_id=:major_id;";
            $pdo=myPDO::getInstance();
            
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":major_id",$major_id);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }

        //获取年级列表
        public function getGradeList($uid='',$grade=0,$major=0){
            $query="select distinct(grade) from tb_instructor_info ORDER BY grade;";
            $pdo=myPDO::getInstance();
            
            $row=$pdo->query($query);
            $result=$row->fetchAll(PDO::FETCH_ASSOC);
            $row->closeCursor();

            return $result;
        }

        public function getClassList(){
            $query="select distinct(class) from tb_student_info_list ORDER BY class;";
            $pdo=myPDO::getInstance();
            
            $row=$pdo->query($query);
            $result=$row->fetchAll(PDO::FETCH_ASSOC);
            $row->closeCursor();

            return $result;
        }

        //获取辅导员年级列表
        public function getInstructorGradeList($instructorID){
            $query="select grade from tb_instructor_info 
            where tb_instructor_info.uid=:uid and status=1 
            group by grade;";
            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }
        
        //获取辅导员专业列表
        public function getInstructorMajorList($instructorID){
            $query="select tb_instructor_info.major_id,tb_major_list.major_name 
            from tb_instructor_info,tb_major_list 
            where tb_instructor_info.uid=:uid and tb_instructor_info.status=1 
            and tb_instructor_info.major_id=tb_major_list.major_id 
            group by tb_instructor_info.major_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }
        
        public function getInstructorClassList($instructorID){
            $query="select class 
            from tb_student_info_list,tb_major_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and (select count(1) as count from tb_instructor_info 
            where tb_instructor_info.uid=:uid 
            and tb_student_info_list.grade=tb_instructor_info.grade 
            and tb_student_info_list.major_id=tb_instructor_info.major_id)>0 
            group by class;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }

        ////获取辅导员年级、专业、班级信息
        public function getInstructorAllocateList($instructorID){
            $query="select grade,tb_major_list.major_id,major_name,class 
            from tb_student_info_list,tb_major_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and (select count(1) as count from tb_instructor_info 
            where tb_instructor_info.uid=:uid 
            and tb_student_info_list.grade=tb_instructor_info.grade 
            and tb_student_info_list.major_id=tb_instructor_info.major_id)>0 
            group by grade,major_id,class;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }

        //获取学生信息
        public function getStudentInformation($student_id){
            $query="select student_id,name,gender,grade,tb_major_list.major_id,
            tb_major_list.major_name,class,phone_number 
            from tb_student_info_list,tb_major_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and student_id=:student_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":student_id",$student_id);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }
        
        public function getStudentDormitoryInfo($student_id){
            $query="select tb_apartment_info.apartment_id,tb_apartment_info.apartment_name,
            floor,dormitory_id 
            from tb_student_dormitory_roster,tb_apartment_info 
            where tb_student_dormitory_roster.status=1 
            and tb_student_dormitory_roster.apartment_id=tb_apartment_info.apartment_id 
            and tb_student_dormitory_roster.student_id=:student_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":student_id",$student_id);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }

        public function getInstructorApartmentList($instructorID){
            $query="select tb_instructor_dormitory_allocating_list.apartment_id,tb_apartment_info.apartment_name 
            from tb_instructor_dormitory_allocating_list,tb_instructor_info,tb_apartment_info 
            where tb_instructor_dormitory_allocating_list.grade=tb_instructor_info.grade 
            and tb_instructor_dormitory_allocating_list.major_id=tb_instructor_info.major_id 
            and tb_apartment_info.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_instructor_info.status=1 
            and tb_instructor_info.uid=:uid 
            group by tb_instructor_dormitory_allocating_list.apartment_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }
        
        public function getInstructorAllocatedBeds($instructorID,$apartment,$dormitoryID){
            $query="select count(1) as count 
            from tb_student_dormitory_roster,tb_student_info_list,tb_instructor_info 
            where tb_student_info_list.student_id=tb_student_dormitory_roster.student_id 
            and tb_student_info_list.status=1 
            and tb_student_dormitory_roster.status=1 
            and tb_instructor_info.status=1 
            and tb_student_info_list.grade=tb_instructor_info.grade 
            and tb_student_info_list.major_id=tb_instructor_info.major_id 
            and tb_student_dormitory_roster.apartment_id=:apartment_id 
            and tb_student_dormitory_roster.dormitory_id=:dormitory_id 
            and tb_instructor_info.uid=:uid;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(":apartment_id",$apartment);
            $statement->bindValue(":dormitory_id",$dormitoryID);
            $statement->bindValue(":uid",$instructorID);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            
            $statement->closeCursor();

            return $result;
        }
    }
?>